package dao;

import bean.Book;
import jdbc.DbConn;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

//封装图书信息的增删查改

public class BookDao {
    private Connection conn;

    public BookDao(){
        this.conn = DbConn.openDB();
    }
    /*
    * 通过图书编号（ISBN）查找图书。
    * 从表tb_Book按图书编号id查找记录
    * 把记录封装成Book对象并返回
    * @param id 图书编号
    * @return Book 图书 bean
    * */

    public Book queryBookById(String id){
        String sql ="select * from tb_book where id = ?";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,id);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()){
                String strId = rs.getString(1);
                if (strId.equals(id)){
                    return new Book(strId, rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),rs.getDouble(6),rs.getString(7), rs.getString(8), rs.getString(9));
                }
            }
        }catch (SQLException ex){
            Logger.getLogger(BookDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return null;
    }

    /*
    通过图书名称查找图书
    从表tb_book按图书名称name查找记录
    把记录封装成Book对象并添加到List对象，最后返回该List对象
    @param name 图书名称
    @return List<Book>图书bean的list独享，空表示没有找到
     */
    public List<Book> queryBooksByName(String name){
        List<Book> list = new ArrayList<Book>();

        String sql = "select * from tb_book where name = ?";

        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);

            ResultSet rs = pstmt.executeQuery();

            while(rs.next()){
                list.add(new Book(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4),rs.getString(5),rs.getDouble(6),rs.getString(7), rs.getString(8), rs.getString(9)));
            }
        }catch (SQLException ex){
            Logger.getLogger(BookDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return list;

    }
    /*
        带条件查找图书
        从表tb_book按条件where查找所有图书记录
        吧记录封装成Book对想并添加到List对象，最后返回该list对象
        @param where 条件字符串 例如name=‘123’ and author=‘456’
        @return List<Book>图书bean的list对象，空表示没有找到
         */

    public List<Book> queryBooksByWhere(String where){
        List<Book> list = new ArrayList<Book>();

        String sql = "select * from tb_book where "+ where;

        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();


            while(rs.next()){
                list.add(new Book(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4),rs.getString(5),rs.getDouble(6),rs.getString(7), rs.getString(8), rs.getString(9)));
            }
        }catch (SQLException ex){
            Logger.getLogger(BookDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return list;

    }
      /*
        查找所有图书资料
        从表tb_book按条件where查找所有图书记录
        吧记录封装成Book对想并添加到List对象，最后返回该list对象
        @param 无
        @return List<Book>图书bean的list对象，空表示没有找到
         */

    public List<Book> queryBooks(){
        List<Book> list = new ArrayList<Book>();

        String sql = "select * from tb_book";

        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();


            while(rs.next()){
                list.add(new Book(rs.getString(1),rs.getString(2), rs.getString(3), rs.getString(4),rs.getString(5),rs.getDouble(6),rs.getString(7), rs.getString(8), rs.getString(9)));
            }
        }catch (SQLException ex){
            Logger.getLogger(BookDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return list;
    }


    /*
    添加一条图书资料
    @param book
    @return boolean true成功 false 失败
     */
    public boolean addBook(Book book){
        String sql = "insert into tb_book(id,name,type,author,publisher,price,abstract,status,mark" + "values(?,?,?,?,?,?,?,?,?)";

        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, book.getId());
            pstmt.setString(2, book.getName());
            pstmt.setString(3, book.getType());
            pstmt.setString(4, book.getAuthor());
            pstmt.setString(5, book.getPublisher());
            pstmt.setDouble(6, book.getPrice());
            pstmt.setString(7, book.getBook_abstract());
            pstmt.setString(8, book.getStatus());
            pstmt.setString(9, book.getMark());

            int x = pstmt.executeUpdate();
            if(x>0){
                return true;
            }
        }catch (SQLException ex){
            Logger.getLogger(BookDao.class.getName()).log(Level.SEVERE,null,ex);
        }
        return false;
    }


     /*
    修改一条图书资料
    @param book
    @return boolean true成功 false 失败
     */
     public boolean modBook(Book book){
         String sql = "update tb_book set name=?,type=?,author=?,publisher=?,price=?,abstract=?,status=?,mark=?"
                 + "where id = ?";

         try{
             PreparedStatement pstmt = conn.prepareStatement(sql);
             pstmt.setString(1, book.getName());
             pstmt.setString(2, book.getType());
             pstmt.setString(3, book.getAuthor());
             pstmt.setString(4, book.getPublisher());
             pstmt.setDouble(5, book.getPrice());
             pstmt.setString(6, book.getBook_abstract());
             pstmt.setString(7, book.getStatus());
             pstmt.setString(8, book.getMark());
             pstmt.setString(9, book.getId());

             int x = pstmt.executeUpdate();
             if(x>0){
                 return true;
             }
         }catch (SQLException ex){
             Logger.getLogger(BookDao.class.getName()).log(Level.SEVERE,null,ex);
         }
         return false;
     }


     /*
    删除一条图书资料
    @param book
    @return boolean true成功 false 失败
     */
     public boolean delBook(Book book){
         String sql = "delete from tb_book where id=?";
         try{
             PreparedStatement pstmt = conn.prepareStatement(sql);
             pstmt.setString(1, book.getId());
             int x = pstmt.executeUpdate();
             if(x>0){
                 return true;
             }
         }catch (SQLException ex){
             Logger.getLogger(BookDao.class.getName()).log(Level.SEVERE,null,ex);
         }
         return false;
     }

    public static void main(String[] args) {
        BookDao dao = new BookDao();
        dao.queryBooks();
    }
}
